const connection = require('../app/database')

class AthletesService {
  async updateAthlete(name, telephone, sex, college_id, id) {
    const statement = `
      UPDATE athletes SET athlete_name = ?,telephone = ?,sex = ? ,college_id = ?
      WHERE athlete_id = ?;
    ` 
    const [res] = await connection.execute(statement,[name,telephone,sex,college_id,id])
    return res
  
  }
  async getAthlete(id) {
    const statement = `
      SELECT a.athlete_id athleteId,a.athlete_name athleteName,a.telephone,a.sex,a.college_id collegeId FROM athletes a
      JOIN users u ON a.user_id = u.user_id
      WHERE u.user_id = ?;
    `
    const [res] = await connection.execute(statement,[id])
    return res
  }
  // async registerItem(athleteId, sportId) {
  //   const statement = `
  //     INSERT INTO athletes_sports (athlete_id,sport_id) VALUES (?,?);
  //   `
  //   const [res] = await connection.execute(statement,[athleteId,sportId])
  //   return res
  // }
  // async getRegisteredItems(id) {
  //   const statement = `
  //     SELECT s.sport_id,s.sport_name,s.sport_time,aths.score,aths.rank FROM athletes_sports aths 
  //     JOIN sports s ON aths.sport_id = s.sport_id
  //     WHERE athlete_id = ?
  //   `
  //   const [res] = await connection.execute(statement,[id])
  //   return res
  // }
  // async uploadScore(athleteId,sportId, score, rank) {
  //   const statement = `
  //     UPDATE athletes_sports SET score = ?,ranking = ? 
  //     WHERE athlete_id = ? AND sport_id = ?;
  //   `
  //   const [res] = await connection.execute(statement,[score,rank,athleteId,sportId])
  //   return res
  // }
  // async getAllAthletesBySports(id) {
  //   const statement = `
  //     SELECT * FROM athletes_sports  aths
  //     JOIN athletes a ON aths.athlete_id = a.athlete_id
  //     JOIN colleges c ON a.college_id = c.college_id
  //     JOIN sports s ON aths.sport_id = s.sport_id
  //     WHERE aths.sport_id = ?
  //     ORDER BY aths.ranking;
  //   `
  //   const [res] = await connection.execute(statement,[id])
  //   return res
  // }
  // async checkState(aid, sid, state) {
  //   const statement = `
  //     UPDATE athletes_sports SET state = ? 
  //     WHERE athlete_id = ? AND sport_id = ?;
  //   `
  //   const [res] = await connection.execute(statement,[state,aid,sid])
  //   return res
  // }
  async getAthleteList(pagenum = '1', pagesize = '10', name = '', telephone = '', sex = '', collegeId = '') {
    const statement = `
      SELECT (SELECT COUNT(*) FROM athletes) totalCount,
      JSON_ARRAYAGG(JSON_OBJECT('id',t.athlete_id,'athleteName',t.athlete_name,'telephone',t.telephone,'sex',t.sex,'collegeName',t.college_name)) list
      FROM	(SELECT a.athlete_id,a.athlete_name,a.telephone,a.sex,c.college_name FROM athletes a JOIN colleges c ON a.college_id = c.college_id
      WHERE a.athlete_name LIKE '%${name}%' AND a.telephone LIKE '%${telephone}%' AND a.sex LIKE '%${sex}%'
      AND c.college_id LIKE '%${collegeId}%'
      LIMIT ?,?) t;
    `
    const offset = (pagenum - 1) * pagesize
    const [res] = await connection.execute(statement,[offset+'',pagesize])
    return res[0]
  }
  async removeAthlete(id) {
    const statement = `
      DELETE FROM athletes WHERE athlete_id = ?;
    `
    const [res] = await connection.execute(statement,[id])
    return res
  }
  async createAthlete(name, telephone, sex, collegeId,userId ) {
    const statement = `
      INSERT INTO athletes (athlete_name,telephone,sex,college_id,user_id)
      VALUES (?,?,?,?,?);
    `
    const [res] = await connection.execute(statement,[name, telephone, sex, collegeId,userId ])
    return res
  }
}

module.exports = new AthletesService()